﻿using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;
using System;

namespace Shuba.Data
{
    public class Cluster
    {
        public Cluster()
        { 
        }

        public List<ClusterInfo> GetList(string value, string CostCenter, string Nip)
        {
            List<ClusterInfo> list = new List<ClusterInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(
                            @"
                            DECLARE @PROCESS_DATE DATETIME 
                            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS 

                            SELECT A.* FROM TBL_MS_CLUSTER A INNER JOIN 
                            (
                                /*SELECT B.CLUSTER_ID FROM TBL_MS_HO_REGION A INNER JOIN TBL_MS_REGION_CLUSTER B ON A.REGIONAL_ID = B.REGIONAL_ID
                                WHERE A.HO_ID = @COST_CENTER
                                UNION
                                SELECT CLUSTER_ID FROM TBL_MS_REGION_CLUSTER WHERE REGIONAL_ID = @COST_CENTER
                                UNION
                                SELECT CLUSTER_ID FROM TBL_MS_REGION_CLUSTER WHERE CLUSTER_ID = @COST_CENTER
                                UNION
                                SELECT CLUSTER_ID FROM TBL_MS_CLUSTER_BRANCH WHERE BRANCH_ID = @COST_CENTER
                                */
                                SELECT A.CLUSTER_ID
                                FROM
                                    TBL_MS_CLUSTER_BRANCH A INNER JOIN 
                                    TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID INNER JOIN
                                    TBL_MS_HO_REGION C ON B.REGIONAL_ID = C.REGIONAL_ID
                                WHERE (C.HO_ID = @COST_CENTER) OR (B.REGIONAL_ID = @COST_CENTER) OR (A.CLUSTER_ID = @COST_CENTER) OR (A.BRANCH_ID = @COST_CENTER)
                                GROUP BY A.CLUSTER_ID
                                UNION
                                SELECT CLUSTER_ID
                                FROM TBL_MS_CLUSTER_BRANCH A INNER JOIN TBL_ALTERNATE_UM C ON A.BRANCH_ID = C.BRANCH_ID
                                WHERE 
                                    (C.NIP = @NIP)
					                AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),C.EFF_DATE_FROM,120)) 
					                AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),C.EFF_DATE_TO,120)) 
					                AND C.IS_ACTIVE = '1'
                                GROUP BY CLUSTER_ID
                            ) X ON A.CLUSTER_ID = X.CLUSTER_ID
                            WHERE A.CLUSTER_NAME LIKE '%' + @CLUSTER_NAME + '%' ORDER BY A.CLUSTER_NAME"
                            );
            db.AddInParameter(cmd, "@CLUSTER_NAME", DbType.String, value);
            db.AddInParameter(cmd, "@COST_CENTER", DbType.String, CostCenter);
            db.AddInParameter(cmd, "@NIP", DbType.String, Nip);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<ClusterInfo> GetListByHO(string HO_ID, string REGIONAL_ID)
        {
            List<ClusterInfo> list = new List<ClusterInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT A.* FROM TBL_MS_CLUSTER A 
	                                                    INNER JOIN TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID
                                                        INNER JOIN TBL_MS_HO_REGION C ON C.REGIONAL_ID = B.REGIONAL_ID
                                                    WHERE C.HO_ID = @HO_ID AND C.REGIONAL_ID LIKE '' + @REGIONAL_ID + '' 
                                                    ORDER BY A.CLUSTER_NAME");
            db.AddInParameter(cmd, "@HO_ID", DbType.String, HO_ID);
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, REGIONAL_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<ClusterInfo> GetListByRegion(string RegionalID)
        {
            List<ClusterInfo> list = new List<ClusterInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(
            @"SELECT C.* FROM TBL_MS_CLUSTER C
              INNER JOIN TBL_MS_REGION_CLUSTER RC ON C.CLUSTER_ID = RC.CLUSTER_ID
              INNER JOIN TBL_MS_REGIONAL R ON RC.REGIONAL_ID = R.REGIONAL_ID
            WHERE R.REGIONAL_ID LIKE '' +  @REGIONAL_ID + ''
            ORDER BY C.CLUSTER_NAME");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<ClusterInfo> GetListByRegion(string RegionalID, string CostCenter, string Nip, string AppID)
        {
            List<ClusterInfo> list = new List<ClusterInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(
            @"DECLARE @PROCESS_DATE DATETIME, @COST_CENTER VARCHAR(6), @USER_TYPE VARCHAR(10) 
            SELECT @PROCESS_DATE = PROCESS_DATE FROM TBL_SYS_PARAMS 

            SELECT TOP 1 @COST_CENTER = BRANCH_ID FROM V_USER 
            WHERE NIP = @NIP AND APP_ID = @APP_ID GROUP BY BRANCH_ID

            SELECT TOP 1 @USER_TYPE = USER_TYPE FROM V_USER
            WHERE NIP = @NIP AND APP_ID = @APP_ID GROUP BY USER_TYPE

            IF (@USER_TYPE = 'LA' OR @USER_TYPE = 'CO' OR @USER_TYPE = 'SO')
            BEGIN
                SELECT A.* FROM TBL_MS_CLUSTER A 
                INNER JOIN 
                (
                    SELECT B.REGIONAL_ID, B.CLUSTER_ID
                    FROM TBL_MS_CLUSTER_BRANCH A INNER JOIN TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID
                    INNER JOIN TBL_ALTERNATE_UM C ON A.BRANCH_ID = C.BRANCH_ID
                    WHERE 
                        (C.NIP = @NIP)
                        AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),C.EFF_DATE_FROM,120)) 
                        AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),C.EFF_DATE_TO,120)) 
                        AND C.IS_ACTIVE = '1'
                    GROUP BY B.REGIONAL_ID, B.CLUSTER_ID
                ) B ON A.CLUSTER_ID = B.CLUSTER_ID
                WHERE B.REGIONAL_ID LIKE '' +  @REGIONAL_ID + ''
                ORDER BY A.CLUSTER_NAME
            END

            ELSE

            SELECT A.* FROM TBL_MS_CLUSTER A 
            INNER JOIN 
            (
                SELECT B.REGIONAL_ID, B.CLUSTER_ID
                FROM
                    TBL_MS_CLUSTER_BRANCH A INNER JOIN 
                    TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID LEFT JOIN
                    TBL_MS_HO_REGION C ON B.REGIONAL_ID = C.REGIONAL_ID
                WHERE (C.HO_ID = @COST_CENTER) OR (B.REGIONAL_ID = @COST_CENTER) OR (A.CLUSTER_ID = @COST_CENTER) OR (A.BRANCH_ID = @COST_CENTER)
                GROUP BY B.REGIONAL_ID, B.CLUSTER_ID
                UNION
                SELECT B.REGIONAL_ID, B.CLUSTER_ID
                FROM TBL_MS_CLUSTER_BRANCH A INNER JOIN TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID
                INNER JOIN TBL_ALTERNATE_UM C ON A.BRANCH_ID = C.BRANCH_ID
                WHERE 
                    (C.NIP = @NIP)
                    AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) >= CONVERT(VARCHAR(10),C.EFF_DATE_FROM,120)) 
                    AND (CONVERT(VARCHAR(10),@PROCESS_DATE,120) <= CONVERT(VARCHAR(10),C.EFF_DATE_TO,120)) 
                    AND C.IS_ACTIVE = '1'
                GROUP BY B.REGIONAL_ID, B.CLUSTER_ID
            ) B ON A.CLUSTER_ID = B.CLUSTER_ID
            WHERE B.REGIONAL_ID LIKE '' +  @REGIONAL_ID + ''
            ORDER BY A.CLUSTER_NAME");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalID);
            //db.AddInParameter(cmd, "@COST_CENTER", DbType.String, CostCenter);
            db.AddInParameter(cmd, "@NIP", DbType.String, Nip);
            db.AddInParameter(cmd, "@APP_ID", DbType.String, AppID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<ClusterInfo> GetListByUnit(string BRANCH_ID)
        {
            List<ClusterInfo> list = new List<ClusterInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_CLUSTER WHERE CLUSTER_ID IN
                                                    (
                                                        SELECT CLUSTER_ID
                                                        FROM TBL_MS_CLUSTER_BRANCH WHERE BRANCH_ID = @BRANCH_ID
                                                    )
                                                    ORDER BY CLUSTER_NAME");
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BRANCH_ID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public List<ClusterInfo> GetListByRegionAndUnit(string RegionalID, string BranchID)
        {
            List<ClusterInfo> list = new List<ClusterInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT * FROM TBL_MS_CLUSTER WHERE CLUSTER_ID IN
                                (
                                    SELECT B.CLUSTER_ID
                                    FROM TBL_MS_CLUSTER_BRANCH AS B INNER JOIN TBL_MS_REGION_CLUSTER AS A 
                                    ON B.CLUSTER_ID = A.CLUSTER_ID
                                    WHERE
                                        (B.BRANCH_ID LIKE @BRANCH_ID) 
                                        AND (A.REGIONAL_ID LIKE @REGIONAL_ID)
                                    GROUP BY B.CLUSTER_ID
                                )
                                ORDER BY CLUSTER_NAME");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalID);
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public string GetClusterIDByUnit(string BranchID)
        {
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT CLUSTER_ID FROM TBL_MS_CLUSTER_BRANCH WHERE BRANCH_ID LIKE @BRANCH_ID ;");
            object result;
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, BranchID);
            result = db.ExecuteScalar(cmd);
            if (result != null)
            {
                return result.ToString();
            }
            else
            {
                return "";
            }
        }

        public List<ClusterInfo> GetClusterApproval(string value, string UpdateBy)
        {
            List<ClusterInfo> list = new List<ClusterInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_CLUSTER_TMP WHERE CLUSTER_NAME LIKE '%' + @CLUSTER_NAME + '%' AND (RTRIM(UPDATE_BY) <> '' + @UPDATE_BY + '') ORDER BY CLUSTER_NAME");
            db.AddInParameter(cmd, "@CLUSTER_NAME", DbType.String, value);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6)));
                }
                dr.Close();
            }
            return list;
        }

        public List<ClusterInfo> GetDependency(string ClusterID)
        {
            List<ClusterInfo> list = new List<ClusterInfo>();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT DISTINCT * FROM
                                (
            	                    SELECT A.* FROM TBL_MS_CLUSTER A INNER JOIN TBL_MS_CLUSTER_BRANCH B ON A.CLUSTER_ID = B.CLUSTER_ID 
            	                    WHERE A.CLUSTER_ID LIKE '' + @CLUSTER_ID + ''
            	                    UNION
            	                    SELECT A.* FROM TBL_MS_CLUSTER A INNER JOIN TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID
            	                    WHERE A.CLUSTER_ID LIKE '' + @CLUSTER_ID + ''
                                ) X
                        ");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterID);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5)));
                }
                dr.Close();
            }
            return list;
        }

        public ClusterInfo Get(string id)
        {
            ClusterInfo ci = new ClusterInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_CLUSTER WHERE CLUSTER_ID LIKE '' + @CLUSTER_ID + ''");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5));
                }
                dr.Close();
            }
            return ci;
        }

        public ClusterInfo GetApprovalByID(string id)
        {
            ClusterInfo ci = new ClusterInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand("SELECT * FROM TBL_MS_CLUSTER_TMP WHERE CLUSTER_ID LIKE '' + @CLUSTER_ID + ''");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, id);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    ci = new ClusterInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetDateTime(3), dr.GetString(4), dr.GetDateTime(5), dr.GetString(6));
                }
                dr.Close();
            }
            return ci;
        }

        public void Save(ClusterInfo cluster)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = @"
                                UPDATE [TBL_MS_CLUSTER_TMP]
                                   SET [CLUSTER_NAME] = @CLUSTER_NAME
                                      ,[ENTRY_BY] = @ENTRY_BY
                                      ,[ENTRY_DATE] = @ENTRY_DATE
                                      ,[UPDATE_BY] = @UPDATE_BY
                                      ,[UPDATE_DATE] = @UPDATE_DATE
                                      ,[ACT_TYPE] = @ACT_TYPE
                                WHERE CLUSTER_ID LIKE '' + @CLUSTER_ID + '';
                                IF @@ROWCOUNT = 0
                                INSERT INTO [TBL_MS_CLUSTER_TMP] ([CLUSTER_ID],[CLUSTER_NAME],[ENTRY_BY],[ENTRY_DATE],[UPDATE_BY],[UPDATE_DATE],[ACT_TYPE]) " +
                                 " VALUES " +
                              " (@CLUSTER_ID, @CLUSTER_NAME, @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE)";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, cluster.ClusterID);
            db.AddInParameter(cmd, "@CLUSTER_NAME", DbType.String, cluster.ClusterName);
            db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, cluster.EntryBy);
            db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, cluster.EntryDate);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, cluster.UpdateBy);
            db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, cluster.UpdateDate);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, cluster.ActType);

            db.ExecuteNonQuery(cmd);
        }

        public void Approve(string clusterId, string ActType)
        {            
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetStoredProcCommand("CLUSTER_APPROVAL");

            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, clusterId);
            db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

            db.ExecuteNonQuery(cmd);
        }

        public void Reject(string clusterId)
        {
            Database db = DBConnection.CreateDatabase();

            string sql = String.Format("DELETE FROM TBL_MS_CLUSTER_TMP WHERE CLUSTER_ID LIKE '{0}'", clusterId);
            DbCommand cmd = db.GetSqlStringCommand(sql);

            db.ExecuteNonQuery(cmd);
        }
    }
}
